﻿if object_id('fn_MapHTML_AssetDistributionTransformer') is not null
	DROP FUNCTION fn_MapHTML_AssetDistributionTransformer

GO

CREATE FUNCTION [dbo].[fn_MapHTML_AssetDistributionTransformer]
(
	@TransformerId int
)
RETURNS varchar(max)
AS
BEGIN
declare @html varchar(max)
set @html = 
(
	select
		'Distribution Transformer' as 'TITLE'
		,Transformers.Utility_Asset_TransformerId
		,STR(Transformers.Latitude, 9, 9) as 'Latitude'
		,STR(Transformers.Longitude, 9, 9)  as 'Longitude'
		,Transformers.CreatedDate
		,Transformers.SerialNumber
		,Transformers.Comments
		,replace(replace(replace(replace(Src.SettingValue, '{0}', Transformers.Utility_Asset_TransformerId), '{1}', 'TransformerAssetDistribution'), '{2}', 85), '{3}', 85) as 'src'
		,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', Transformers.Utility_Asset_TransformerId), '{1}', 'TransformerAssetDistribution'), '{2}', 640), '{3}', 480)) as 'href'
		,Transformers.ImageFileName
	from
		tb_Utility_Asset_Transformers as Transformers
	left join tb_Settings as Href 
		on Href.SettingId = 25
	left join tb_Settings as Src 
		on Src.SettingId = 24
	where
		Transformers.Utility_Asset_TransformerId = @TransformerId
	for xml path ('Transformer')

	)
	return @html

END

 